#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🔍 验证前端映射的完整性和一致性...\n');

// 1. 检查两种实例表结构
console.log('1. 分析实例表的两种结构模式:');

// 检查标准结构（使用record_id）
const standardTable = 'device_instances_instrumentation_controller';
const standardColumns = db.prepare(`PRAGMA table_info(${standardTable})`).all();
const hasRecordIds = standardColumns.some(col => col.name.endsWith('_record_id'));
const hasDirectFields = standardColumns.some(col => 
  col.name.startsWith('naming_') || col.name.startsWith('position_') || col.name.startsWith('base_params_')
);

console.log(`\n标准表 ${standardTable}:`);
console.log(`  使用record_id引用: ${hasRecordIds ? '✓' : '✗'}`);
console.log(`  使用直接字段嵌入: ${hasDirectFields ? '✓' : '✗'}`);

// 检查模板导入结构（使用直接字段）
const templateTable = 'device_instances_piping_cooling_water_spray_system';
const templateColumns = db.prepare(`PRAGMA table_info(${templateTable})`).all();
const templateHasRecordIds = templateColumns.some(col => col.name.endsWith('_record_id'));
const templateHasDirectFields = templateColumns.some(col => 
  col.name.startsWith('naming_') || col.name.startsWith('position_') || col.name.startsWith('base_params_')
);

console.log(`\n模板导入表 ${templateTable}:`);
console.log(`  使用record_id引用: ${templateHasRecordIds ? '✓' : '✗'}`);
console.log(`  使用直接字段嵌入: ${templateHasDirectFields ? '✓' : '✗'}`);

// 2. 检查所有实例表的结构模式
console.log('\n2. 统计所有实例表的结构模式:');
const allInstanceTables = db.prepare(`
  SELECT name FROM sqlite_master 
  WHERE type='table' AND name LIKE 'device_instances_%'
`).all();

let recordIdTables = 0;
let directFieldTables = 0;
let mixedTables = 0;

allInstanceTables.forEach(table => {
  const columns = db.prepare(`PRAGMA table_info(${table.name})`).all();
  const hasRecordIds = columns.some(col => col.name.endsWith('_record_id'));
  const hasDirectFields = columns.some(col => 
    col.name.startsWith('naming_') || col.name.startsWith('position_') || col.name.startsWith('base_params_')
  );
  
  if (hasRecordIds && hasDirectFields) {
    mixedTables++;
  } else if (hasRecordIds) {
    recordIdTables++;
  } else if (hasDirectFields) {
    directFieldTables++;
  }
});

console.log(`总实例表数量: ${allInstanceTables.length}`);
console.log(`仅使用record_id引用: ${recordIdTables} 个`);
console.log(`仅使用直接字段嵌入: ${directFieldTables} 个`);
console.log(`混合模式: ${mixedTables} 个`);

// 3. 检查API是否正确处理两种模式
console.log('\n3. 验证API处理两种映射模式的能力:');

// 模拟device-instances API的GET请求处理逻辑
function simulateInstanceRetrieval(deviceTypeCode, tableName) {
  console.log(`\n模拟获取 ${deviceTypeCode} 的实例数据:`);
  console.log(`  使用表: ${tableName}`);
  
  try {
    const instances = db.prepare(`
      SELECT * FROM [${tableName}]
      WHERE device_type_code = ?
      ORDER BY created_at
    `).all(deviceTypeCode);
    
    console.log(`  找到实例: ${instances.length} 个`);
    
    if (instances.length > 0) {
      const instance = instances[0];
      console.log(`  实例结构分析:`);
      
      // 检查record_id字段
      if (instance.naming_record_id !== undefined || instance.position_record_id !== undefined || instance.base_params_record_id !== undefined) {
        console.log(`    使用record_id引用模式:`);
        if (instance.naming_record_id) console.log(`      naming_record_id: ${instance.naming_record_id}`);
        if (instance.position_record_id) console.log(`      position_record_id: ${instance.position_record_id}`);
        if (instance.base_params_record_id) console.log(`      base_params_record_id: ${instance.base_params_record_id}`);
      }
      
      // 检查直接字段
      const directFields = Object.keys(instance).filter(key => 
        key.startsWith('naming_') || key.startsWith('position_') || key.startsWith('base_params_')
      );
      
      if (directFields.length > 0) {
        console.log(`    使用直接字段嵌入模式:`);
        directFields.forEach(field => {
          if (instance[field] !== null && instance[field] !== '') {
            console.log(`      ${field}: ${instance[field]}`);
          }
        });
      }
    }
    
    return instances;
  } catch (error) {
    console.log(`  错误: ${error.message}`);
    return [];
  }
}

// 测试标准表
simulateInstanceRetrieval('仪表-0015', 'device_instances_instrumentation_controller');

// 测试模板导入表
simulateInstanceRetrieval('配管-0047', 'device_instances_piping_cooling_water_spray_system');

// 4. 验证前端参数数据的获取方式
console.log('\n4. 验证前端参数数据获取的一致性:');

// 方式1: 通过record_id获取参数数据
function getParametersByRecordId(recordId, tableName) {
  if (!recordId || !tableName) return null;
  
  try {
    const paramData = db.prepare(`SELECT * FROM ${tableName} WHERE id = ?`).get(recordId);
    return paramData;
  } catch (error) {
    console.log(`获取参数表 ${tableName} 记录 ${recordId} 失败: ${error.message}`);
    return null;
  }
}

// 方式2: 直接从实例表字段获取参数数据
function getParametersFromInstanceFields(instance) {
  const parameters = {
    naming: {},
    position: {},
    base_params: {}
  };
  
  Object.keys(instance).forEach(key => {
    if (key.startsWith('naming_') && instance[key] !== null && instance[key] !== '') {
      parameters.naming[key.replace('naming_', '')] = instance[key];
    } else if (key.startsWith('position_') && instance[key] !== null && instance[key] !== '') {
      parameters.position[key.replace('position_', '')] = instance[key];
    } else if (key.startsWith('base_params_') && instance[key] !== null && instance[key] !== '') {
      parameters.base_params[key.replace('base_params_', '')] = instance[key];
    }
  });
  
  return parameters;
}

console.log('\n测试参数数据获取:');

// 测试有数据的实例
const pipingInstance = db.prepare(`SELECT * FROM device_instances_piping_cooling_water_spray_system LIMIT 1`).get();
if (pipingInstance) {
  console.log('\n配管实例的参数数据:');
  const directParams = getParametersFromInstanceFields(pipingInstance);
  console.log('  命名参数:', directParams.naming);
  console.log('  位置参数:', directParams.position);
  console.log('  基础参数:', directParams.base_params);
}

// 5. 检查前端需要的统一数据格式
console.log('\n5. 检查前端所需的统一数据格式:');

function normalizeInstanceData(deviceTypeCode, tableName) {
  const instances = db.prepare(`
    SELECT * FROM [${tableName}]
    WHERE device_type_code = ?
  `).all(deviceTypeCode);
  
  return instances.map(instance => {
    // 基础实例信息
    const normalized = {
      id: instance.id,
      device_type_code: instance.device_type_code,
      instance_number: instance.instance_number,
      object_number: instance.object_number,
      object_description: instance.object_description,
      created_at: instance.created_at,
      updated_at: instance.updated_at
    };
    
    // 参数数据（统一格式）
    normalized.parameters = {
      naming: {},
      position: {},
      base_params: {}
    };
    
    // 处理record_id模式
    if (instance.naming_record_id || instance.position_record_id || instance.base_params_record_id) {
      // 获取设备映射信息
      const deviceInfo = db.prepare(`
        SELECT dtm.* FROM devices d
        LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
        WHERE d.device_id = ?
      `).get(deviceTypeCode);
      
      if (deviceInfo) {
        if (instance.naming_record_id && deviceInfo.naming_table) {
          const namingData = getParametersByRecordId(instance.naming_record_id, deviceInfo.naming_table);
          if (namingData) {
            Object.keys(namingData).forEach(key => {
              if (key !== 'id' && key !== 'created_at' && key !== 'updated_at') {
                normalized.parameters.naming[key] = namingData[key];
              }
            });
          }
        }
        
        if (instance.position_record_id && deviceInfo.position_table) {
          const positionData = getParametersByRecordId(instance.position_record_id, deviceInfo.position_table);
          if (positionData) {
            Object.keys(positionData).forEach(key => {
              if (key !== 'id' && key !== 'created_at' && key !== 'updated_at') {
                normalized.parameters.position[key] = positionData[key];
              }
            });
          }
        }
        
        if (instance.base_params_record_id && deviceInfo.base_params_table) {
          const baseData = getParametersByRecordId(instance.base_params_record_id, deviceInfo.base_params_table);
          if (baseData) {
            Object.keys(baseData).forEach(key => {
              if (key !== 'id' && key !== 'created_at' && key !== 'updated_at') {
                normalized.parameters.base_params[key] = baseData[key];
              }
            });
          }
        }
      }
    }
    
    // 处理直接字段模式
    Object.keys(instance).forEach(key => {
      if (key.startsWith('naming_') && instance[key] !== null && instance[key] !== '') {
        normalized.parameters.naming[key.replace('naming_', '')] = instance[key];
      } else if (key.startsWith('position_') && instance[key] !== null && instance[key] !== '') {
        normalized.parameters.position[key.replace('position_', '')] = instance[key];
      } else if (key.startsWith('base_params_') && instance[key] !== null && instance[key] !== '') {
        normalized.parameters.base_params[key.replace('base_params_', '')] = instance[key];
      }
    });
    
    return normalized;
  });
}

console.log('\n测试统一数据格式:');
const normalizedPipingData = normalizeInstanceData('配管-0047', 'device_instances_piping_cooling_water_spray_system');
if (normalizedPipingData.length > 0) {
  console.log('配管实例统一格式:');
  console.log(JSON.stringify(normalizedPipingData[0], null, 2));
}

console.log('\n✅ 前端映射验证完成');
console.log('\n总结:');
console.log('1. ✓ 识别出两种实例表结构模式');
console.log('2. ✓ record_id引用模式需要关联查询参数表');
console.log('3. ✓ 直接字段嵌入模式可直接获取参数数据');
console.log('4. ✓ 需要在API中统一处理两种模式');
console.log('5. ✓ 前端可以接收统一格式的参数数据');

db.close();